package com.general.app.wjgl.service.impl;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.Region;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;

import javax.servlet.http.HttpServletResponse;

import com.general.app.wjgl.dao.ISurveyRebackDao;
import com.general.app.wjgl.dao.entity.SurveyRebackModel;
import com.general.app.wjgl.dao.entity.SurveySendModel;
import com.general.app.wjgl.service.ISurveyRebackService;
import com.general.app.xtgl.dao.entity.AssessUserModel;
import com.general.common.security.entity.User;
import com.general.common.service.impl.BaseServiceImpl;
import com.general.utils.base.Constant;
import com.general.utils.base.StringUtil;

public class SurveyRebackServiceImpl extends BaseServiceImpl<SurveyRebackModel, ISurveyRebackDao>
implements ISurveyRebackService{

	/**
	 * 个人回收列表
	 * @param model
	 * @return
	 */
	public List<SurveyRebackModel> getPagedUserList(SurveyRebackModel model){
		return dao.getPagedUserList(model);
	}

	public HSSFWorkbook createWorkBook(){
		return null;
	}
	/**
	 * 取得问卷信息
	 * @param ids
	 * @param tab
	 * @return
	 */
	public SurveyRebackModel getRackModel(String ids, String tab){

		SurveyRebackModel backModel = new SurveyRebackModel();
		// 解析选择记录
		if (!StringUtil.isEmpty(tab) && !StringUtil.isEmpty(ids)) {
			
			// 查看单位问卷发放信息
			if(Constant.TAB_COMPANY.equalsIgnoreCase(tab)){
				SurveyRebackModel model = new SurveyRebackModel();
				model.setSend_id(ids);
				
				List<SurveyRebackModel> lst = dao.getPagedList(model);
				if(lst !=null 
						&& lst.size()!=0){
					backModel = lst.get(0);
				}
				
				// 查看个人发放信息
			}else if(Constant.TAB_SINGEL.equalsIgnoreCase(tab)){

				SurveyRebackModel model = new SurveyRebackModel();
				model.setSend_id(ids);

				List<SurveyRebackModel> lst = dao.getPagedUserList(model);
				if(lst !=null 
						&& lst.size()!=0){
					backModel = lst.get(0);
				}
			}
		}
		
		return backModel;
	}
	
	/**
	 * 【问卷因子平均分】统计情况
	 * @param model
	 * @return
	 */
	public List<SurveyRebackModel> getSurveyFactorAverageList(SurveyRebackModel model)
	{
		String send_survey_user_id = model.getSend_id();
		// 不为空
		if(!StringUtil.isEmpty(send_survey_user_id)){
			
			String[] ids = send_survey_user_id.split("_");
			model.setSend_id(ids[0]);
			model.setSurvey_id(ids[1]);
			model.setAssess_user_id(ids[2]);
			return dao.getSurveyFactorAverageList(model);
		}else{
			return null;
		}
	}
	/**
	 * 【不同评估者得分】统计情况
	 * @param model
	 * @return
	 */
	public List<SurveyRebackModel> getAssessRebackList(SurveyRebackModel model){

		String send_survey_user_id = model.getSend_id();
		// 不为空
		if(!StringUtil.isEmpty(send_survey_user_id)){
			
			String[] ids = send_survey_user_id.split("_");
			model.setSend_id(ids[0]);
			model.setSurvey_id(ids[1]);
			model.setAssess_user_id(ids[2]);
			return dao.getAssessRebackList(model);
		}else{
			return null;
		}
	}
	
	

	/**
	 * 【360评分全貌图】列表
	 * @param model
	 * @return
	 */
	public List<SurveyRebackModel> getAllQuestionList(SurveyRebackModel model){
		
		String send_survey_user_id = model.getSend_id();
		// 不为空
		if(!StringUtil.isEmpty(send_survey_user_id)){
			
			String[] ids = send_survey_user_id.split("_");
			model.setSend_id(ids[0]);
			model.setSurvey_id(ids[1]);
			model.setAssess_user_id(ids[2]);
			return dao.getAllQuestionList(model);
		}else{
			return null;
		}
	}
	

	/**
	 * 【开放性问题】列表 STYLE_ID：3
	 * @param model
	 * @return
	 */
	public List<SurveyRebackModel> getOpenQuestionList(SurveyRebackModel model){
		String send_survey_user_id = model.getSend_id();
		// 不为空
		if(!StringUtil.isEmpty(send_survey_user_id)){
			
			String[] ids = send_survey_user_id.split("_");
			model.setSend_id(ids[0]);
			model.setSurvey_id(ids[1]);
			model.setAssess_user_id(ids[2]);
			return dao.getOpenQuestionList(model);
		}else{
			return null;
		}
	}
	

	/**
	 * 【个别特殊问题】列表 spemark：2
	 * @param model
	 * @return
	 */
	public List<SurveyRebackModel> getSpeQuestionList(SurveyRebackModel model){

		String send_survey_user_id = model.getSend_id();
		// 不为空
		if(!StringUtil.isEmpty(send_survey_user_id)){
			
			String[] ids = send_survey_user_id.split("_");
			model.setSend_id(ids[0]);
			model.setSurvey_id(ids[1]);
			model.setAssess_user_id(ids[2]);
			return dao.getSpeQuestionList(model);
		}else{
			return null;
		}
	}

	/**
	 * 导出个人统计信息
	 * 
	 * @param map
	 * @param user
	 * @param response
	 */
	public HSSFWorkbook dcUserSurStat(Map<String, Object> map, User user,
			HttpServletResponse response) {

		HSSFWorkbook wb = new HSSFWorkbook();
		try {

			// 个人简单信息取得
			AssessUserModel assuserModel = (AssessUserModel) map
					.get("assessmodel");

			// 个人问卷回收信息
			SurveyRebackModel rebackModel = (SurveyRebackModel) map
					.get("rebackmodel");

			// ==============开始生成excel=======================
			HSSFSheet sheet = wb.createSheet(rebackModel.getAssess_user_nm());
			//初始化
			this.initSheet(sheet);
			
			//设置为水平居中  
			HSSFCellStyle centerStyle = wb.createCellStyle();
			centerStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
			centerStyle.setBorderLeft(HSSFCellStyle.BORDER_THICK);
			centerStyle.setBorderRight(HSSFCellStyle.BORDER_THICK);
			centerStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
	        centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
	        centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	        centerStyle.setFillForegroundColor(HSSFColor.LIME.index);
	        centerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	        
	         //水平靠右  
	        HSSFCellStyle rightStyle = wb.createCellStyle();
	        rightStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);  
	        rightStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
	        
	         //水平左
	        HSSFCellStyle leftStyle = wb.createCellStyle();
	        rightStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	        rightStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
	        
	        // 设置字体    
	        HSSFFont headfont = wb.createFont();    
	        headfont.setFontName("黑体");    
	        headfont.setFontHeightInPoints((short) 22);// 字体大小    
	        headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗 
	        centerStyle.setFont(headfont);
	        
	        // 设置字体    
	        HSSFFont leftTitleFont = wb.createFont();    
	        leftTitleFont.setFontName("黑体");    
	        leftTitleFont.setFontHeightInPoints((short) 12);// 字体大小    
	        leftTitleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗 
	        leftStyle.setFont(leftTitleFont);
	        

	        // 设置字体    
	        HSSFFont showFont = wb.createFont();    
	        showFont.setFontName("宋体");    
	        showFont.setFontHeightInPoints((short) 12);// 字体大小    
	        showFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 加粗 

	        // 浅黄底带边框样式(表头用)
	        HSSFCellStyle showTitleStyle = wb.createCellStyle();
	        showTitleStyle.cloneStyleFrom(leftStyle);
	        showTitleStyle.setFont(showFont);
	        showTitleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	        showTitleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	        showTitleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	        showTitleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	        showTitleStyle.setFillForegroundColor(HSSFColor.TAN.index);
	        showTitleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	        
	        // 白底带边框样式
	        HSSFCellStyle showStyle = wb.createCellStyle();
	        showStyle.cloneStyleFrom(leftStyle);
	        showStyle.setFont(showFont);
	        showStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	        showStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	        showStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	        showStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	        
	        // 淡色蓝底边框样式
	        HSSFCellStyle showTuiStyle = wb.createCellStyle();
	        showTuiStyle.cloneStyleFrom(showStyle);
	        showTuiStyle.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
	        showTuiStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

	        // 土黄底边框样式
	        HSSFCellStyle showTanStyle = wb.createCellStyle();
	        showTanStyle.cloneStyleFrom(showStyle);
	        showTanStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
	        showTanStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	        
	        // -----------------------标题----------------------------
	        //第一行设置标题
	        int intStart = 0;
	        HSSFRow row1 = sheet.createRow((short) intStart);
	        intStart++;
	        createCell(row1, 0, "360问卷调查", centerStyle);
	        createCellColumn(row1, 1, centerStyle, new String[]{"","","","","","","","","",""});
	        CellRangeAddress craddrs = new CellRangeAddress(0,0,0,10);
	        sheet.addMergedRegion(craddrs);

	        // -----------------------被评估人信息----------------------------
	        // 第三行
	        sheet.createRow((short) intStart);
	        intStart++;
	        HSSFRow row3 = sheet.createRow((short) intStart);
	        intStart++;
	        createCell(row3, 0, "被评估人", leftStyle);	        
	        // 第四行
	        HSSFRow row4 = sheet.createRow((short) intStart);
	        createCell(row4, 0, "", leftStyle);
	        createCell(row4, 1, "姓名", showTitleStyle);
	        createCell(row4, 2, rebackModel.getAssess_user_nm(), showStyle);
	        createCellColumn(row4, 3, showStyle, new String[]{"","",""});
	        craddrs = new CellRangeAddress(intStart,intStart,2,5);
	        sheet.addMergedRegion(craddrs);
	        intStart++;
	        // 第五行
	        HSSFRow row5 = sheet.createRow((short) intStart);
	        createCell(row5, 0, "", leftStyle);
	        createCell(row5, 1, "邮箱", showTitleStyle);
	        createCell(row5, 2, assuserModel.getUser_email(), showStyle);
	        createCellColumn(row5, 3, showStyle, new String[]{"","",""});
	        craddrs = new CellRangeAddress(intStart,intStart,2,5);
	        sheet.addMergedRegion(craddrs);
	        intStart++;
	        // 第六行
	        HSSFRow row6 = sheet.createRow((short) intStart);
	        createCell(row6, 0, "", leftStyle);
	        createCell(row6, 1, "手机号码", showTitleStyle);
	        createCell(row6, 2, assuserModel.getUser_phone(), showStyle);
	        createCellColumn(row6, 3, showStyle, new String[]{"","",""});
	        craddrs = new CellRangeAddress(intStart,intStart,2,5);
	        sheet.addMergedRegion(craddrs);
	        intStart++;
	        // 第六行
	        HSSFRow row7 = sheet.createRow((short) intStart);
	        createCell(row7, 0, "", leftStyle);
	        createCell(row7, 1, "单位名称", showTitleStyle);
	        createCell(row7, 2, assuserModel.getCompany_name(), showStyle);
	        createCellColumn(row7, 3, showStyle, new String[]{"","",""});
	        craddrs = new CellRangeAddress(intStart,intStart,2,5);
	        sheet.addMergedRegion(craddrs);
	        intStart++;
	        // 第七行
	        HSSFRow row8 = sheet.createRow((short) intStart);
	        createCell(row8, 0, "", leftStyle);
	        createCell(row8, 1, "部门", showTitleStyle);
	        createCell(row8, 2, assuserModel.getDepartment(), showStyle);
	        createCellColumn(row8, 3, showStyle, new String[]{"","",""});
	        craddrs = new CellRangeAddress(intStart,intStart,2,5);
	        sheet.addMergedRegion(craddrs);
	        intStart++;
	        // 第八行
	        HSSFRow row9 = sheet.createRow((short) intStart);
	        createCell(row9, 0, "", leftStyle);
	        createCell(row9, 1, "职位", showTitleStyle);
	        createCell(row9, 2, assuserModel.getPosition(), showStyle);
	        createCellColumn(row9, 3, showStyle, new String[]{"","",""});
	        craddrs = new CellRangeAddress(intStart,intStart,2,5);
	        sheet.addMergedRegion(craddrs);
	        intStart++;	   

	        // -----------------------问卷信息----------------------------
	        //问卷名称
	        sheet.createRow((short) intStart);
	        intStart++;
	        HSSFRow row11 = sheet.createRow((short) intStart);
	        intStart++;
	        createCell(row11, 0, "问卷名称", leftStyle);
	        createCell(row11, 1, rebackModel.getSurvey_name(), leftStyle);	    

	        // -----------------------问卷回收信息----------------------------
	        sheet.createRow((short) intStart);
	        intStart++;
	        HSSFRow row12 = sheet.createRow((short) intStart);
	        intStart++;
	        createCell(row12, 0, "问卷回收情况", leftStyle);
	        HSSFRow row13 = sheet.createRow((short) intStart);
	        intStart++;
	        String[] inputSurveyReback = new String[]{"回收状态", "发放数", "回答[已提交]", "保存数[未提交]", "未回答数", "问卷回收率"};
	        createCellColumn(row13, 1, showTitleStyle, inputSurveyReback);
	        

	        // -----------------------问卷因子平均分----------------------------
	        HSSFRow row14 = sheet.createRow((short) intStart);
	        intStart++;
	        String[] inputReback = new String[]{rebackModel.getSurvey_flg_name(), rebackModel.getSend_counts()
	        		, rebackModel.getSubmit_counts(), rebackModel.getSave_counts(), rebackModel.getUnanswered_counts(), rebackModel.getRecycling_rate()};
	        createCellColumn(row14, 1, showStyle, inputReback);
	        sheet.createRow((short) intStart);
	        intStart++;
	        HSSFRow row15 = sheet.createRow((short) intStart);
	        intStart++;
	        createCell(row15, 0, "问卷因子平均分", leftStyle);
	        HSSFRow row16 = sheet.createRow((short) intStart);
	        intStart++;
	        String[] inputFactorHead = new String[]{"问卷因子", "本人得分", "全员平均分", "全员最高得分"};
	        createCellColumn(row16, 1, showTitleStyle, inputFactorHead);

			// 【问卷因子平均分】统计情况
			List<SurveyRebackModel> surveyFactorAverageList = (List<SurveyRebackModel>) map
					.get("surveyFactorAverageList");

	        // 问卷因子平均分列表
	        if(surveyFactorAverageList !=null && surveyFactorAverageList.size() > 0 ){
	        	for(int i=0;i< surveyFactorAverageList.size() ;i++){

			        HSSFRow rowX = sheet.createRow((short) intStart);
			        intStart++;
			        String[] inputsurveyFactor = new String[]{surveyFactorAverageList.get(i).getFactor_name()
			        		, surveyFactorAverageList.get(i).getSelf_score(), surveyFactorAverageList.get(i).getWhole_self_score(),
			        		surveyFactorAverageList.get(i).getWhole_high_score()};
			        
			        createCellColumn(rowX, 1, showTuiStyle, inputsurveyFactor, showStyle);
	        	}
	        }

	        // -----------------------不同评估者得分----------------------------

			// 【不同评估者得分】统计情况
			List<SurveyRebackModel> assessRebackList = (List<SurveyRebackModel>) map
					.get("assessRebackList");
	        sheet.createRow((short) intStart);
	        intStart++;
	        HSSFRow row17 = sheet.createRow((short) intStart);
	        intStart++;
	        createCell(row17, 0, "不同评估者得分", leftStyle);
	        HSSFRow row18 = sheet.createRow((short) intStart);
	        intStart++;
	        String[] inputAssessRebackHead = new String[]{"评估者", "本人得分", "全员平均分", "全员最高得分"};
	        createCellColumn(row18, 1, showTitleStyle, inputAssessRebackHead);
	        
	        // 不同评估者得分列表
	        if(assessRebackList !=null && assessRebackList.size() > 0 ){
	        	for(int i=0;i< assessRebackList.size() ;i++){

			        HSSFRow rowX = sheet.createRow((short) intStart);
			        intStart++;
			        String[] inputAssessReback = new String[]{assessRebackList.get(i).getRelation_name()
			        		, assessRebackList.get(i).getSelf_score(), assessRebackList.get(i).getWhole_self_score(),
			        		assessRebackList.get(i).getWhole_high_score()};
			        createCellColumn(rowX, 1, showTuiStyle, inputAssessReback, showStyle);
	        	}
	        }

	        // -----------------------开放式问题----------------------------
			// 【开放性问题】列表 STYLE_ID：3
			List<SurveyRebackModel> openQuestionGet = (List<SurveyRebackModel>) map
					.get("openQuestionList");
			
			if(openQuestionGet!=null && openQuestionGet.size()>0){
		        sheet.createRow((short) intStart);
		        intStart++;
		        HSSFRow row19 = sheet.createRow((short) intStart);
		        intStart++;
		        createCell(row19, 0, "开放式问题", leftStyle);
		        
		        // 循环问题
				for(int i=0;i<openQuestionGet.size(); i++){
					
					HSSFRow rowCreat = sheet.createRow((short) intStart);
			        String[] inputCreatHead = new String[]{"开放性问题",
			        		"【"+ openQuestionGet.get(i).getKeyword() +"】"+ openQuestionGet.get(i).getQuestion_name(),
			        		"","","","","","","",""};
			        createCellColumn(rowCreat, 1, showTitleStyle, inputCreatHead, showTanStyle);
			        CellRangeAddress cradd = new CellRangeAddress(intStart,intStart,2,10);
			        sheet.addMergedRegion(cradd);
			        intStart++;
			        
			        // 评价详情
			        if(openQuestionGet.get(i).getOpenRelationList()!=null
			        		&& openQuestionGet.get(i).getOpenRelationList().size() >0){
			        	for(int j=0; j < openQuestionGet.get(i).getOpenRelationList().size(); j++){
			        		
							HSSFRow rowContent = sheet.createRow((short) intStart);
				        	String[] inputCreat = new String[]{openQuestionGet.get(i).getOpenRelationList().get(j).getName(),
				        			openQuestionGet.get(i).getOpenRelationList().get(j).getContent(),
				        			"","","","","","","",""};
					        createCellColumn(rowContent, 1, showTuiStyle, inputCreat, showStyle);
					        createCellColumn(rowCreat, 1, showTitleStyle, inputCreatHead, showTanStyle);
					        CellRangeAddress cra = new CellRangeAddress(intStart,intStart,2,10);
					        sheet.addMergedRegion(cra);
							intStart++;
			        	}
			        	
			        }
				}
			}

	        // -----------------------个别特殊问题----------------------------
			// 【个别特殊问题】列表 spemark：2
			List<SurveyRebackModel> speQuestionList = (List<SurveyRebackModel>) map
					.get("speQuestionList");

			if(speQuestionList!=null && speQuestionList.size()>0){

		        sheet.createRow((short) intStart);
		        intStart++;
		        HSSFRow row20 = sheet.createRow((short) intStart);
		        intStart++;
		        createCell(row20, 0, "个别特殊问题", leftStyle);

		        // 循环问题
				for(int i=0;i<speQuestionList.size(); i++){
					
					HSSFRow rowCreat = sheet.createRow((short) intStart);
			        String[] inputCreatHead = new String[]{"个别特殊问题",
			        		"【"+ speQuestionList.get(i).getKeyword() +"】"+ speQuestionList.get(i).getQuestion_name(),
			        		"","","","","","","",""};
			        createCellColumn(rowCreat, 1, showTitleStyle, inputCreatHead, showTanStyle);
			        CellRangeAddress cradd = new CellRangeAddress(intStart,intStart,2,10);
			        sheet.addMergedRegion(cradd);
			        intStart++;
			        
					HSSFRow rowCreat1 = sheet.createRow((short) intStart);
			        createCell(rowCreat1, 1, "自评得分", showTuiStyle);
			        createCell(rowCreat1, 2, speQuestionList.get(i).getQuestion_sum_zp()+ "分（整体平均得分："+speQuestionList.get(i).getQuestion_total_zp()+"）", showStyle);
			        createCellColumn(rowCreat1, 3, showStyle, new String[]{"","","","","","","",""});
			        CellRangeAddress cra = new CellRangeAddress(intStart,intStart,2,10);
			        sheet.addMergedRegion(cra);
			        intStart++;
			        
			        
			        HSSFRow rowCreat2 = sheet.createRow((short) intStart);
			        createCell(rowCreat2, 1, "他评得分", showTuiStyle);
			        createCell(rowCreat2, 2, speQuestionList.get(i).getQuestion_sum_tp()+ "分（整体平均得分："+speQuestionList.get(i).getQuestion_total_tp()+"）", showStyle);
			        createCellColumn(rowCreat2, 3, showStyle, new String[]{"","","","","","","",""});
			        CellRangeAddress cra2 = new CellRangeAddress(intStart,intStart,2,10);
			        sheet.addMergedRegion(cra2);
			        intStart++;
				}
			}

	        // -----------------------360评分全貌图----------------------------
			// 【360评分全貌图】列表
			List<SurveyRebackModel> allQuestiongGet = (List<SurveyRebackModel>) map
					.get("allQuestionList");
			
			if(allQuestiongGet!=null && allQuestiongGet.size()>0){
		        sheet.createRow((short) intStart);
		        intStart++;
		        HSSFRow row21 = sheet.createRow((short) intStart);
		        intStart++;
		        createCell(row21, 0, "360评分全貌图", leftStyle);
			    
		        HSSFRow row22 = sheet.createRow((short) intStart);

		        // 上级:CORAL底边框样式
		        HSSFCellStyle showCoralStyle = wb.createCellStyle();
		        showCoralStyle.cloneStyleFrom(showStyle);
		        showCoralStyle.setFillForegroundColor(HSSFColor.CORAL.index);
		        showCoralStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		        

		        // 平级:淡蓝底边框样式
		        HSSFCellStyle showGreenStyle = wb.createCellStyle();
		        showGreenStyle.cloneStyleFrom(showStyle);
		        showGreenStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
		        showGreenStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		        

		        // 下级:紫色底边框样式
		        HSSFCellStyle showLavenderStyle = wb.createCellStyle();
		        showLavenderStyle.cloneStyleFrom(showStyle);
		        showLavenderStyle.setFillForegroundColor(HSSFColor.LAVENDER.index);
		        showLavenderStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		        

		        // 自评：蓝色底边框样式
		        HSSFCellStyle showPaleStyle = wb.createCellStyle();
		        showPaleStyle.cloneStyleFrom(showStyle);
		        showPaleStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
		        showPaleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		        showTitleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
		        createCell(row22, 1, "360评分全貌图", showTitleStyle);
		        createCellColumn(row22, 2, showTitleStyle, new String[]{"","","",""});
		        CellRangeAddress cra2 = new CellRangeAddress(intStart,intStart,1,5);
		        sheet.addMergedRegion(cra2);
		        createCell(row22, 6, "上级", showCoralStyle);
		        createCell(row22, 7, "平级", showGreenStyle);
		        createCell(row22, 8, "下级", showLavenderStyle);
		        createCell(row22, 9, "自评", showPaleStyle);
		        intStart++;
		        
		        // 循环问题
				for(int i=0;i<allQuestiongGet.size(); i++){
					HSSFRow rowCreat = sheet.createRow((short) intStart);

			        showTitleStyle.setWrapText(true);
			        createCell(rowCreat, 1, "【"+ allQuestiongGet.get(i).getKeyword() +"】"+ allQuestiongGet.get(i).getQuestion_name(), 
			        		showTanStyle);

			        createCellColumn(rowCreat, 2, showTitleStyle, new String[]{"","","",""});
			        CellRangeAddress cra = new CellRangeAddress(intStart,intStart,1,5);
			        sheet.addMergedRegion(cra);
			        // 评分
			        if(allQuestiongGet.get(i).getOpenRelationList()!=null
			        		&& allQuestiongGet.get(i).getOpenRelationList().size() >0){
					        createCell(rowCreat, 6, allQuestiongGet.get(i).getOpenRelationList().get(0).getScore(), showCoralStyle);
					        createCell(rowCreat, 7, allQuestiongGet.get(i).getOpenRelationList().get(1).getScore(), showGreenStyle);
					        createCell(rowCreat, 8, allQuestiongGet.get(i).getOpenRelationList().get(2).getScore(), showLavenderStyle);
					        createCell(rowCreat, 9, allQuestiongGet.get(i).getOpenRelationList().get(3).getScore(), showPaleStyle);
			        }
			        intStart++;
				}
			}



		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return wb;
	}
	
	/**
	 * 输出一行
	 * @param row
	 * @param startColumn
	 * @param style
	 * @param inputs
	 */
	private void createCellColumn(HSSFRow row,int startColumn, HSSFCellStyle  style, String[] inputs){  

	       //((Object) cell).setEncoding((short) 1);  
	       for(int i=0; i< inputs.length ;i++){
		       HSSFCell cell = row.createCell((short)startColumn);
		       cell.setCellValue(String.valueOf(inputs[i].toString()));  
		       cell.setCellStyle(style);
		       startColumn ++;
	       }
		
	}
	
	/**
	 * 输出一行（2个样式)
	 * @param row
	 * @param startColumn
	 * @param style
	 * @param inputs
	 */
	private void createCellColumn(HSSFRow row,int startColumn, HSSFCellStyle  style, String[] inputs, HSSFCellStyle  style2){  

	       //((Object) cell).setEncoding((short) 1);  
	       for(int i=0; i< inputs.length ;i++){
		       HSSFCell cell = row.createCell((short)startColumn);
		       cell.setCellValue(String.valueOf(inputs[i].toString())); 
		       if(i==0){
		    	   cell.setCellStyle(style);
		       }else{
		    	   cell.setCellStyle(style2);
		       }
		       startColumn ++;
	       }
		
	}
	
	/**  
	    * 创建单元格  
	    * @param row 行  
	    * @param column 列位置  
	    * @param value 值  
	    * @param style 样式  
	    */  
	   private void createCell(HSSFRow row,int column,Object value,HSSFCellStyle style){  
	       HSSFCell cell = row.createCell((short)column);  
	       //cell.setEncoding((short) 1);  
	       cell.setCellValue(String.valueOf(value));  
	       cell.setCellStyle(style);  
	   }
	//初始化sheet，设置列数和每列宽度  
	@SuppressWarnings("deprecation")
	private void initSheet(HSSFSheet sheet){  
	    sheet.setColumnWidth((short)0, (short)(35.7 * 130));  
	    sheet.setColumnWidth((short)1, (short)(35.7 * 120));  
	    sheet.setColumnWidth((short)2, (short)(35.7 * 150));  
	    sheet.setColumnWidth((short)3, (short)(35.7 * 120));  
	    sheet.setColumnWidth((short)4, (short)(35.7 * 120));  
	    sheet.setColumnWidth((short)5, (short)(35.7 * 120));   
	    sheet.setColumnWidth((short)6, (short)(35.7 * 90));   
	    sheet.setColumnWidth((short)7, (short)(35.7 * 80));   
	    sheet.setColumnWidth((short)8, (short)(35.7 * 80));   
	    sheet.setColumnWidth((short)9, (short)(35.7 * 80));  
	}  
}
